Database administrators (DBAs) use indexes to speed up large table lookup in query workloads. Indexes are built over columns that appear in many predicates or are referred to often, and the indexes can be implemented as a B-tree.
In multi-column indexes, a first column of the index key (leading column) is stored in sorted order for fast B-tree searching, while non-leading columns might need index scanning. DBAs can create many multi column indexes with different column combinations and permutations to satisfy different predicate variations. In single-column indexes, there is access to multiple indexes for predicates across different columns. Index ANDings or ORings are needed, and multiple record ID (RID) sorts are often needed as well. ANDing refers to finding an intersection of two or more record lists. ORing refers to finding unions of two or more record lists.
However, operations and costs exist when using an index for query evaluation. For example, index use requires B-tree lookup on indexes, sorting of record identification (ID) lists, and intersections of record ID (RID) lists. Additionally, an index needs to be updated at the same time as data is updated and/or inserted, which can slow down online transaction processing (OLTP) workloads and/or add more tasks to an extract, transform and load (ETL) process.